package com.sky.service.impl;

import com.sky.dto.GoodsSalesDTO;
import com.sky.entity.Orders;
import com.sky.mapper.OrderMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.ReportService;
import com.sky.service.WorkspaceService;
import com.sky.vo.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.net.URL;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * 营业额统计
 */
@Service
public class ReportServiceImpl implements ReportService {

    @Autowired
    OrderMapper orderMapper;
    @Autowired
    UserMapper userMapper;
    @Autowired
    WorkspaceService workspaceService;

    /**
     * 营业额统计
     * @param begin
     * @param end
     * @return
     */
    @Override
    public TurnoverReportVO getTurnoverStatistics(LocalDate begin, LocalDate end) {
         ArrayList<LocalDate> ld= new ArrayList<>();
         //日期，以逗号分隔，例如：2022-10-01,2022-10-02,2022-10-03
         ld.add(begin);
         while (!begin.equals(end))
         {
             begin=begin.plusDays(1);
             ld.add(begin);
         }
        //营业额，以逗号分隔，例如：406.0,1520.0,75.0
         ArrayList<Double> turnovers = new ArrayList<>();
        for (LocalDate date : ld) {
             //select sum(amount) form orders where order_time>#{min} and order_time<#{max} and status=#{status}
             LocalDateTime min = LocalDateTime.of(date, LocalTime.MIN);
             LocalDateTime  max= LocalDateTime.of(date, LocalTime.MAX);
             HashMap<String, Object> map = new HashMap<>();
             map.put("min",min);
             map.put("max",max);
             map.put("status",5);
             Double turnover=orderMapper.sumBymap(map);
            turnover=turnover==null?0.0:turnover;
            turnovers.add(turnover);

        }

        return TurnoverReportVO
                .builder()
                .dateList(StringUtils.join(ld,","))
                .turnoverList(StringUtils.join(turnovers,","))
                .build();
    }

    /**
     * 用户统计
     * @param begin
     * @param end
     * @return
     */
    @Override
    public UserReportVO getUserStatistics(LocalDate begin, LocalDate end) {
        ArrayList<LocalDate> ld = new ArrayList<>();
        //日期，以逗号分隔，例如：2022-10-01,2022-10-02,2022-10-03
         ld.add(begin);
         while (!begin.equals(end))
         {
             begin=begin.plusDays(1);
             ld.add(begin);
         }
        //用户总量，以逗号分隔，例如：200,210,220 (截止到某天用户总量)
        //新增用户，以逗号分隔，例如：20,21,10(某天新增的用户)
         ArrayList<Integer> userTotals = new ArrayList<>();
         ArrayList<Integer> userNews = new ArrayList<>();
        for (LocalDate date : ld) {
             LocalDateTime min = LocalDateTime.of(date, LocalTime.MIN);
             LocalDateTime max= LocalDateTime.of(date, LocalTime.MAX);
            HashMap<String, Object> map = new HashMap<>();
            map.put("max",max);
           Integer userTotal= userMapper.countBymap(map);
           userTotals.add(userTotal);
            map.put("min",min);
           Integer userNew= userMapper.countBymap(map);
           userNews.add(userNew);

        }

        return UserReportVO.builder()
                .dateList(StringUtils.join(ld,","))
                .totalUserList(StringUtils.join(userTotals,","))
                .newUserList(StringUtils.join(userNews,","))
                .build();
    }

    /**
     * 订单统计
     * @param begin
     * @param end
     * @return
     */
    @Override
    public OrderReportVO getOrdersStatistics(LocalDate begin, LocalDate end) {
        ArrayList<LocalDate> ld = new ArrayList<>();
        //日期，以逗号分隔，例如：2022-10-01,2022-10-02,2022-10-03
        ld.add(begin);
        while (!begin.equals(end))
        {
            begin=begin.plusDays(1);
            ld.add(begin);
        }
        
        ArrayList<Integer> orderTotalList = new ArrayList<>();
        ArrayList<Integer> validOrderList = new ArrayList<>();
        //每日订单数，以逗号分隔，例如：260,210,215
        //每日有效订单数，以逗号分隔，例如：20,21,10
        for (LocalDate date : ld) {
             LocalDateTime min = LocalDateTime.of(date, LocalTime.MIN);
             LocalDateTime max= LocalDateTime.of(date, LocalTime.MAX);
             //select count(id) form orders where create_time>#{} and create_time <#{}
             Integer o1= getOrderCount(min, max, null);
             //select count(id) form orders where create_time>#{} and create_time <#{} and status=5
             Integer o2 = getOrderCount(min, max, Orders.COMPLETED);
             orderTotalList.add(o1);
             validOrderList.add(o2);
             
        }

       
        //订单总数
        Integer orderTotal = orderTotalList.stream().reduce(Integer::sum).get();
        //有效订单数
        Integer validOrder = validOrderList.stream().reduce(Integer::sum).get();
        //订单完成率
        Double  orderCompleteRate=0.0;
        if (orderTotal!=0)
        {
            orderCompleteRate= validOrder.doubleValue()/orderTotal;
        }

        return OrderReportVO.builder()
                .dateList(StringUtils.join(ld,","))
                .orderCountList(StringUtils.join(orderTotalList,","))
                .validOrderCountList(StringUtils.join(validOrderList,","))
                .totalOrderCount(orderTotal)
                .validOrderCount(validOrder)
                .orderCompletionRate(orderCompleteRate)
                .build();
    }



    /**
     * 获得订单数
     * @param begin
     * @param end
     * @param status
     * @return
     */
    public Integer getOrderCount(LocalDateTime begin,LocalDateTime end,Integer status)
    {
         HashMap<String, Object> map = new HashMap<>();
         map.put("min",begin);
         map.put("max",end);
         map.put("status",status);
       return orderMapper.countBymap(map);
    }

    /**
     * 查询销量排名top10
     * @param begin
     * @param end
     * @return
     */
    @Override
    public SalesTop10ReportVO getTop10(LocalDate begin, LocalDate end) {
       LocalDateTime min= LocalDateTime.of(begin,LocalTime.MIN);
       LocalDateTime max=LocalDateTime.of(end,LocalTime.MAX);
       List<GoodsSalesDTO> gl=orderMapper.getSaleTop10(min,max);
       List<String> names= gl.stream().map(GoodsSalesDTO::getName).collect(Collectors.toList());
       List<Integer> numbers = gl.stream().map(GoodsSalesDTO::getNumber).collect(Collectors.toList());


        return SalesTop10ReportVO.builder().nameList(StringUtils.join(names,","))
                .numberList(StringUtils.join(numbers,","))
                .build();

    }

    /**
     * 导出Excel报表
     * @param response
     */
    @Override
    public void getExcelExport(HttpServletResponse response) {
         //统计最近30天
         LocalDate start = LocalDate.now().minusDays(30);
         LocalDate end = LocalDate.now().minusDays(1);
         //获得营业数据
         BusinessDataVO data =
                 workspaceService.getBusinessData
                         (LocalDateTime.of(start, LocalTime.MIN), LocalDateTime.of(end, LocalTime.MAX));
//         豆包说路径如果包含中文和空格就不能用这个
//         String path = this.getClass().getResource("template/运营数据报表模板.xlsx").getPath();
         InputStream inputStream= this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");
        try {
             XSSFWorkbook xss = new XSSFWorkbook(inputStream);
             XSSFSheet sheet = xss.getSheet("Sheet1");
             XSSFRow row1 = sheet.getRow(1);
             //日期
             row1.getCell(1).setCellValue("日期:"+start+"-"+end);
             //营业额
             XSSFRow row3 = sheet.getRow(3);
             row3.getCell(2).setCellValue(data.getTurnover());
             //订单完成率
             row3.getCell(4).setCellValue(data.getOrderCompletionRate());
             //新增用户
             row3.getCell(6).setCellValue(data.getNewUsers());
             XSSFRow row4 = sheet.getRow(4);
             //有效订单
              row4.getCell(2).setCellValue(data.getValidOrderCount());
              row4.getCell(4).setCellValue(data.getUnitPrice());

              for (int i=0;i<30;i++)
              {
                  LocalDate begin = start.plusDays(i);

                  //获得当天的营业数据
                  BusinessDataVO date = workspaceService.getBusinessData(LocalDateTime.of(begin, LocalTime.MIN), LocalDateTime.of(begin, LocalTime.MAX));
                  XSSFRow row = sheet.getRow(7+i);
                  //每天日期
                  row.getCell(1).setCellValue(begin.toString());
                  //营业额
                  row.getCell(2).setCellValue(date.getTurnover());
                  //有效订单数
                  row.getCell(3).setCellValue(date.getValidOrderCount());
                  //订单完成率
                  row.getCell(4).setCellValue(date.getOrderCompletionRate());
                  //平均客单价
                  row.getCell(5).setCellValue(date.getUnitPrice());
                  //新增用户数
                  row.getCell(6).setCellValue(date.getNewUsers());
              }

            //将报表下载到浏览器客户端
            ServletOutputStream outputStream = response.getOutputStream();
            xss.write(outputStream);

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}
